-- FlowPlan用户管理相关存储过程
-- 说明: 提供用户管理相关的存储过程

-- 创建用户存储过程
CREATE OR REPLACE PROCEDURE create_user(
    IN p_username VARCHAR(50),
    IN p_email VARCHAR(100),
    IN p_password_hash VARCHAR(255),
    IN p_role VARCHAR(20),
    IN p_org_id BIGINT,
    OUT p_user_id BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO sys_user (username, email, password_hash, role, org_id)
    VALUES (p_username, p_email, p_password_hash, p_role, p_org_id)
    RETURNING id INTO p_user_id;
    
    -- 记录审计日志
    INSERT INTO audit_log (operator_id, operation_type, resource_type, resource_id, new_value)
    VALUES (p_user_id, 'CREATE', 'USER', p_user_id, 
            jsonb_build_object('username', p_username, 'email', p_email, 'role', p_role));
EXCEPTION
    WHEN unique_violation THEN
        RAISE EXCEPTION '用户名或邮箱已存在';
END;
$$;

-- 更新用户状态存储过程
CREATE OR REPLACE PROCEDURE update_user_status(
    IN p_user_id BIGINT,
    IN p_is_active BOOLEAN,
    IN p_operator_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_old_status BOOLEAN;
BEGIN
    -- 获取旧状态
    SELECT is_active INTO v_old_status FROM sys_user WHERE id = p_user_id;
    
    -- 更新用户状态
    UPDATE sys_user 
    SET is_active = p_is_active, update_time = CURRENT_TIMESTAMP
    WHERE id = p_user_id;
    
    -- 记录审计日志
    INSERT INTO audit_log (operator_id, operation_type, resource_type, resource_id, 
                          old_value, new_value)
    VALUES (p_operator_id, 'UPDATE', 'USER', p_user_id,
            jsonb_build_object('is_active', v_old_status),
            jsonb_build_object('is_active', p_is_active));
EXCEPTION
    WHEN no_data_found THEN
        RAISE EXCEPTION '用户不存在';
END;
$$;

-- 批量删除用户存储过程
CREATE OR REPLACE PROCEDURE batch_delete_users(
    IN p_user_ids BIGINT[],
    IN p_operator_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id BIGINT;
    v_deleted_count INTEGER := 0;
BEGIN
    -- 遍历用户ID数组
    FOREACH v_user_id IN ARRAY p_user_ids
    LOOP
        -- 删除用户相关的数据
        DELETE FROM excel_todo_rel WHERE todo_id IN (SELECT id FROM todo WHERE assignee_id = v_user_id);
        DELETE FROM note WHERE author_id = v_user_id;
        DELETE FROM todo WHERE assignee_id = v_user_id OR reporter_id = v_user_id;
        DELETE FROM excel_plan WHERE upload_user_id = v_user_id;
        DELETE FROM project WHERE creator_id = v_user_id;
        
        -- 删除用户
        DELETE FROM sys_user WHERE id = v_user_id;
        
        -- 记录审计日志
        INSERT INTO audit_log (operator_id, operation_type, resource_type, resource_id)
        VALUES (p_operator_id, 'DELETE', 'USER', v_user_id);
        
        v_deleted_count := v_deleted_count + 1;
    END LOOP;
    
    RAISE NOTICE '成功删除 % 个用户', v_deleted_count;
END;
$$;